﻿using System;
using PPI.Entities.Distribution;
using System.Data.SqlClient;
using PPI.Data;
using System.Data;

namespace PPIDataAccess.Distribution
{
    public class DTransformer
    {
        public static int Insert(ETransformer transformer, Int32 poleId, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;

            try
            {
                cmd = new SqlCommand("up_tb_Vendor_Distribution_Transformers_Insert", DBHelper.GetConnection(connectionKey));
                tr = cmd.Connection.BeginTransaction();
                cmd.Transaction = tr;

                cmd.CommandType = CommandType.StoredProcedure;

                #region Pole

                if (poleId == Int32.MinValue)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Vendor_Distribution_PoleFk", ParameterDirection.Input, true, DBNull.Value));
                else
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Vendor_Distribution_PoleFk", ParameterDirection.Input, true, poleId));

                #endregion

                #region - Latitude / Longitude -

                if (transformer.Latitude == double.MinValue)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Latitude", ParameterDirection.Input, true, DBNull.Value));
                else
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, transformer.Latitude));

                if (transformer.Longitude == double.MinValue)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Longitude", ParameterDirection.Input, true, DBNull.Value));
                else
                    cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, transformer.Longitude));

                #endregion

                #region Number

                if (string.IsNullOrEmpty(transformer.SerialNumber))
                    cmd.Parameters.Add(
                        DBHelper.CreateNVarCharParameter("@SerialNumber", 100, ParameterDirection.Input, true, DBNull.Value));
                else
                    cmd.Parameters.Add(
                        DBHelper.CreateNVarCharParameter("@SerialNumber", 100, ParameterDirection.Input, true, transformer.SerialNumber));

                #endregion

                #region Comments

                if (string.IsNullOrEmpty(transformer.Comments))
                    cmd.Parameters.Add(DBHelper.CreateTextParameter("@Comments", ParameterDirection.Input, true, DBNull.Value));
                else
                    cmd.Parameters.Add(DBHelper.CreateTextParameter("@Comments", ParameterDirection.Input, true, transformer.Comments));

                #endregion

                #region Created date

                if (transformer.CreatedDate == DateTime.MinValue)
                    cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@CreatedDate", ParameterDirection.Input, true, DBNull.Value));
                else
                    cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@CreatedDate", ParameterDirection.Input, true, transformer.CreatedDate));

                #endregion

                if (string.IsNullOrEmpty(transformer.UtilityNumber))
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@UtilityNumber", 100, ParameterDirection.Input, true, DBNull.Value));
                else
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@UtilityNumber", 100, ParameterDirection.Input, true, transformer.UtilityNumber));

                if (transformer.TransformerType != null && transformer.TransformerType.Id != int.MinValue && transformer.TransformerType.Id > 0)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@TransformerTypeFk", ParameterDirection.Input, true, transformer.TransformerType.Id));
                else
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@TransformerTypeFk", ParameterDirection.Input, true, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Manufacturer))
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Manufacturer", 100, ParameterDirection.Input, true, transformer.Manufacturer));
                else
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Manufacturer", 100, ParameterDirection.Input, true, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Primary))
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Primary", 100, ParameterDirection.Input, true, transformer.Primary));
                else
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Primary", 100, ParameterDirection.Input, true, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Secondary))
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Secondary", 100, ParameterDirection.Input, true, transformer.Secondary));
                else
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Secondary", 100, ParameterDirection.Input, true, DBNull.Value));

                if (transformer.Type != null && transformer.Type.Id != int.MinValue && transformer.Type.Id > 0)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@TypeFk", ParameterDirection.Input, true, transformer.Type.Id));
                else
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@TypeFk", ParameterDirection.Input, true, DBNull.Value));

                if (transformer.Class != null && transformer.Class.Id != int.MinValue && transformer.Class.Id > 0)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, true, transformer.Class.Id));
                else
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, true, DBNull.Value));

                if (transformer.Phase != null && transformer.Phase.Id != int.MinValue && transformer.Phase.Id > 0)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@PhaseFk", ParameterDirection.Input, true, transformer.Phase.Id));
                else
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@PhaseFk", ParameterDirection.Input, true, DBNull.Value));

                cmd.Parameters.Add(DBHelper.CreateBitParameter("@Arrestor", ParameterDirection.Input, true, transformer.Arrestor));
                cmd.Parameters.Add(DBHelper.CreateBitParameter("@DualRated", ParameterDirection.Input, true, transformer.DualRated));
                cmd.Parameters.Add(DBHelper.CreateBitParameter("@Pre1978", ParameterDirection.Input, true, transformer.Pre1978));
                cmd.Parameters.Add(DBHelper.CreateBitParameter("@CutOut", ParameterDirection.Input, true, transformer.CutOut));

                if (transformer.PCBType != null && transformer.PCBType.Id != int.MinValue && transformer.PCBType.Id > 0)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@PCBTypeFk", ParameterDirection.Input, true, transformer.PCBType.Id));
                else
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@PCBTypeFk", ParameterDirection.Input, true, DBNull.Value));

                if (transformer.ArrestorKv != null && transformer.ArrestorKv.Id != int.MinValue && transformer.ArrestorKv.Id > 0)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@ArrestorKv", ParameterDirection.Input, true, transformer.ArrestorKv.Id));
                else
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@ArrestorKv", ParameterDirection.Input, true, DBNull.Value));

                if (transformer.CutOutKv != null && transformer.CutOutKv.Id != int.MinValue && transformer.CutOutKv.Id > 0)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@CutOutKv", ParameterDirection.Input, true, transformer.CutOutKv.Id));
                else
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@CutOutKv", ParameterDirection.Input, true, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.PPM))
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@PPM", 100, ParameterDirection.Input, true, transformer.PPM));
                else
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@PPM", 100, ParameterDirection.Input, true, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.TransformerNumber))
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@TransformerNumber", 100, ParameterDirection.Input, true, transformer.TransformerNumber));
                else
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@TransformerNumber", 100, ParameterDirection.Input, true, DBNull.Value));

                cmd.Parameters.Add(DBHelper.CreateIntParameter("@Vendor_DistributionTransformer_TransformerId", ParameterDirection.ReturnValue, false, 0));

                if (transformer.Can != null && transformer.Can.Id != int.MinValue && transformer.Can.Id > 0)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@CanFk", ParameterDirection.Input, true, transformer.Can.Id));
                else
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@CanFk", ParameterDirection.Input, true, DBNull.Value));

                if (transformer.DeterminedBy != null && transformer.DeterminedBy.Id != int.MinValue && transformer.DeterminedBy.Id > 0)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@DeterminedBy", ParameterDirection.Input, true, transformer.DeterminedBy.Id));
                else
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@DeterminedBy", ParameterDirection.Input, true, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.TestNumber))
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@TestNumber", 100, ParameterDirection.Input, true, transformer.TestNumber));
                else
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@TestNumber", 100, ParameterDirection.Input, true, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Impeddance))
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Impeddance", 100, ParameterDirection.Input, true, transformer.Impeddance));
                else
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Impeddance", 100, ParameterDirection.Input, true, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Taps))
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Taps", 100, ParameterDirection.Input, true, transformer.Taps));
                else
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Taps", 100, ParameterDirection.Input, true, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Polarity))
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Polarity", 100, ParameterDirection.Input, true, transformer.Polarity));
                else
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Polarity", 100, ParameterDirection.Input, true, DBNull.Value));

                if (transformer.ManufDate != DateTime.MinValue)
                    cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@ManufDate", ParameterDirection.Input, true, transformer.ManufDate));
                else
                    cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@ManufDate", ParameterDirection.Input, true, DBNull.Value));

                cmd.Parameters.Add(DBHelper.CreateBitParameter("@Sticker", ParameterDirection.Input, true, transformer.Sticker));

                cmd.ExecuteNonQuery();
                tr.Commit();

                return (Int32)cmd.Parameters["@Vendor_DistributionTransformer_TransformerId"].Value;
            }
            catch (Exception ex)
            {
                if (tr != null)
                    tr.Rollback();

                return 0;
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }
        }

        public static void InsertNote(Int32 idTransformer, Int32 idNote, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;

            try
            {
                cmd = new SqlCommand("up_tb_Vendor_Distribution_NotesXTransformer_Insert", DBHelper.GetConnection(connectionKey));
                tr = cmd.Connection.BeginTransaction();
                cmd.Transaction = tr;

                cmd.CommandType = CommandType.StoredProcedure;

                #region Note

                if (idNote == Int32.MinValue)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@noteFk", ParameterDirection.Input, true, DBNull.Value));
                else
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@noteFk", ParameterDirection.Input, true, Convert.ToInt32(idNote)));

                #endregion

                #region Meter Id

                if (idTransformer == Int32.MinValue)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@idTransformer", ParameterDirection.Input, true, DBNull.Value));
                else
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@idTransformer", ParameterDirection.Input, true, idTransformer));

                #endregion

                cmd.ExecuteNonQuery();
                tr.Commit();
            }
            catch (Exception ex)
            {
                if (tr != null)
                    tr.Rollback();
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }
        }
    }
}
